-- Indexes (at least 3)
CREATE INDEX idx_patient_name ON Patients(name);
CREATE INDEX idx_appointment_date ON Appointments(date);
CREATE INDEX idx_staff_department ON Medical_Staff(department_id);

-- Stored Functions (at least 3)
CREATE OR REPLACE FUNCTION calculate_department_revenue(dept_id INTEGER) RETURNS DECIMAL AS $$
DECLARE
    total DECIMAL;
BEGIN
    SELECT SUM(i.amount) INTO total
    FROM Invoices i
    JOIN Appointments a ON i.appointment_id = a.appointment_id
    JOIN Medical_Staff s ON a.staff_id = s.staff_id
    WHERE s.department_id = dept_id AND i.status = 'paid';
    RETURN COALESCE(total, 0);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION check_doctor_availability(doc_id INTEGER, app_date DATE, app_time TIME) RETURNS BOOLEAN AS $$
BEGIN
    RETURN NOT EXISTS (
        SELECT 1 FROM Appointments
        WHERE staff_id = doc_id AND date = app_date AND time = app_time
    );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_medication_stock(med_id INTEGER, qty INTEGER) RETURNS VOID AS $$
BEGIN
    UPDATE Medications SET stock = stock - qty WHERE medication_id = med_id AND stock >= qty;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Insufficient stock for medication %', med_id;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Triggers (at least 3)
CREATE OR REPLACE FUNCTION trigger_update_room_status() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'completed' THEN
        UPDATE Rooms SET status = 'available' WHERE room_id = NEW.room_id;
    ELSIF NEW.status = 'scheduled' THEN
        UPDATE Rooms SET status = 'occupied' WHERE room_id = NEW.room_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_room_status
AFTER UPDATE ON Appointments
FOR EACH ROW EXECUTE FUNCTION trigger_update_room_status();

CREATE OR REPLACE FUNCTION trigger_validate_appointment() RETURNS TRIGGER AS $$
BEGIN
    IF NOT check_doctor_availability(NEW.staff_id, NEW.date, NEW.time) THEN
        RAISE EXCEPTION 'Doctor is not available at that time.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_appointment
BEFORE INSERT ON Appointments
FOR EACH ROW EXECUTE FUNCTION trigger_validate_appointment();

CREATE OR REPLACE FUNCTION trigger_update_stock() RETURNS TRIGGER AS $$
BEGIN
    PERFORM update_medication_stock(NEW.medication_id, NEW.quantity);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_stock_after_prescription
AFTER INSERT ON Prescription_Medications
FOR EACH ROW EXECUTE FUNCTION trigger_update_stock();